#!/usr/bin/python
import sqlite3
import string

#Create the database
sqlite3initSQL= ["""
CREATE TABLE IF NOT EXISTS tasks (uuid text PRIMARY KEY, status text)
""","""
CREATE TABLE IF NOT EXISTS history (uuid text, time long, old string, new string, FOREIGN KEY (uuid) REFERENCES tasks (uuid) ON DELETE CASCADE)
"""]

def createDB(dbPath):
    commands = [ x.strip() for x in sqlite3initSQL ]
    print commands

    con = sqlite3.connect(dbPath)

    cur = con.cursor()

    map(lambda x: cur.execute(x), commands)

    con.commit()

    con.close()
    

def insertTasks(dbPath, rows):

    con = sqlite3.connect(dbPath)

    cur = con.cursor()

    cur.execute("SELECT * FROM tasks")
    oldColumns = set()
    map(lambda x: oldColumns.add(x[0]), cur.description)

    newColumns = set()
    for row in rows:
        map(lambda x: newColumns.add(x), row.keys())

    # Append new columns
    if not oldColumns >= newColumns:
        map(lambda x: cur.execute("ALTER TABLE tasks ADD COLUMN " + x), newColumns - oldColumns)

    # Finally is safe to insert items
    for row in rows:
        command = "INSERT INTO tasks (\'" +  string.join(row.keys(), "\', \'") + "\') Values (" + string.join(['?'] * len(row.keys()), ', ') + ")"
        cur.execute(command, map(lambda x: row[x], row.keys()))


    con.commit()

    con.close()

def getTasks(dbPath):

    con = sqlite3.connect(dbPath)

    con.row_factory = sqlite3.Row

    cur = con.cursor()

    cur.execute("SELECT * FROM tasks")
    columns = []
    map(lambda x: columns.append(x[0]), cur.description)

    SQLres = cur.execute("SELECT * FROM tasks")

    result = map(lambda row: { key:row[key] for key in filter(lambda x: row[x] != None, columns) }, SQLres)

    con.close()

    return result


def insertHistory(dbPath, records):

    con = sqlite3.connect(dbPath)

    cur = con.cursor()

    for record in records:
        if "old" in record:
            command = "INSERT INTO history (uuid, time, old, new) VALUES (?,?,?,?)"
            var = ("\'" + record["new"]["uuid"] + "\'", record["time"], "\'" + str(record["old"]) + "\'", "\'" + str(record["new"]) + "\'", )
        else:
            command = "INSERT INTO history (uuid, time, new) VALUES (?,?,?)"
            var = ("\'" + record["new"]["uuid"] + "\'", record["time"], "\'" + str(record["new"]) + "\'", )
        cur.execute(command, var)
        
    con.commit()
            
    con.close()

def extractHistory(dbPath):

    con = sqlite3.connect(dbPath)

    con.row_factory = sqlite3.Row

    cur = con.cursor()

    SQLres = cur.execute("SELECT time,old,new FROM history ORDER BY time ASC")

    
    result = map(lambda row: { key:row[key] for key in filter(lambda x: row[x] != None, [ "time", "old", "new" ]) }, SQLres)

    con.close()

    return result

def processHistory(dbPath, records):

    con = sqlite3.connect(dbPath)

    cur = con.cursor()

    # Expand table tasks columns if needed 
    cur.execute("SELECT * FROM tasks")
    oldColumns = set()
    map(lambda x: oldColumns.add(x[0]), cur.description)

    newColumns = set()
    for record in records:
        map(lambda x: newColumns.add(x), record["new"].keys())

    # Append new columns
    if not oldColumns >= newColumns:
        map(lambda x: cur.execute("ALTER TABLE tasks ADD COLUMN " + x), newColumns - oldColumns)

    # Proces the records:
    for record in records:
        if "old" in record:
            # Update record
            updateDict = { key:None for key in filter(lambda key: key not in record, newColumns | oldColumns) }
            updateDict = dict(updateDict.items() + record["new"].items())
            command = "UPDATE tasks SET \'" + string.join(updateDict.keys(),"\'=?,\'") + "\'=? WHERE \'uuid\'=\'" + updateDict["uuid"] +  "\'" 
            cur.execute(command, map(lambda x: updateDict[x], updateDict.keys()))

            # Insert into History table
            command = "INSERT INTO history (uuid, time, old, new) VALUES (?,?,?,?)"
            var = ("\'" + record["new"]["uuid"] + "\'", record["time"], "\'" + str(record["old"]) + "\'", "\'" + str(record["new"]) + "\'", )
            cur.execute(command, var)
        else:
            # New record
            command = "INSERT INTO tasks (\'" +  string.join(record["new"].keys(), "\', \'") + "\') Values (" + string.join(['?'] * len(record["new"].keys()), ', ') + ")"
            cur.execute(command, map(lambda x: record["new"][x], record["new"].keys()))

            # Insert into History table
            command = "INSERT INTO history (uuid, time, new) VALUES (?,?,?)"
            var = ("\'" + record["new"]["uuid"] + "\'", record["time"], "\'" + str(record["new"]) + "\'", )
            cur.execute(command, var)

    con.commit()

    con.close()
